package com.ctshk.rpc.order.goods.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ctshk.rpc.order.goods.dto.ShoppingShopStoreDTO;
import com.ctshk.rpc.order.goods.entity.ShoppingShopStore;
import com.ctshk.rpc.order.goods.req.ShoppingShopStorePageReq;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * TODO
 *
 * @author 黄利斌
 * @date 2021/6/1 4:23 下午
 */
public interface ShoppingShopStoreMapper extends BaseMapper<ShoppingShopStore> {
    /**
     * 根据小店名称、成员名称模糊查询成员信息
     * @param page
     * @param pageReq
     * @return
     */
    @Select({
            "<script>" +
                    "SELECT t.id,t.store_invitation_code,t.head_portrait_url,t.store_name,t.staff_id, t.staff_name, t.shop_id, t.shop_name,t.shop_type,t.status,t.gmt_create," +
                    "(SELECT COUNT(1) + 1 FROM `ctshk-goods`.t_shopping_shop_store s WHERE s.store_id = t.id) AS \"totalMemberNumber\""+
                    "FROM `ctshk-goods`.t_shopping_shop_store t"+
                    "<where> 1=1 AND t.store_type = 2 AND t.staff_type = 2 "+ // store_type：1-总店，2-分店；staff_type:1-管理员，2-成员
                    "<if test='pageReq.storeNameOrstaffName!=null and pageReq.storeNameOrstaffName!=\"\"'> AND (t.staff_name like concat('%',#{pageReq.storeNameOrstaffName},'%')) OR (t.store_name like concat('%',#{pageReq.storeNameOrstaffName},'%'))</if>"+
                    "<if test='pageReq.storeId!=null'>AND t.store_id = #{pageReq.storeId}</if>"+
                    "</where>"+
                    "order by t.gmt_create DESC"+
                    "</script>"
    })
    IPage<ShoppingShopStoreDTO> findShoppingShopStoreByCondition(IPage<ShoppingShopStoreDTO> page, ShoppingShopStorePageReq pageReq);

    /**
     * 根据联络电话查询关联的小店信息
     * @param contactNumber
     * @return
     */
    @Select({
            "SELECT * FROM `ctshk-goods`.t_shopping_shop_store WHERE customer_service_hotline = #{contactNumber}"
    })
    ShoppingShopStore selectShopByPhone(@Param("contactNumber") String contactNumber);

    @Select({
            "SELECT sss.*,(SELECT sst.staff_name FROM `ctshk-goods`.t_shopping_shop_store sst WHERE sst.id = sss.store_id) AS parentName\n" +
                    "FROM `ctshk-goods`.t_shopping_shop_store sss " +
                    "WHERE sss.customer_service_hotline = #{contactNumber} and sss.password = #{password}"
    })
    ShoppingShopStore selectShopByPhoneAndPassword(@Param("contactNumber") String contactNumber,@Param("password") String password);

    /**
     * 根据电子邮箱查询关联的小店信息
     * @param contactNumber
     * @return
     */
    @Select({
            "SELECT\n" +
                    "sss.*,\n" +
                    "(SELECT sst.staff_name FROM `ctshk-goods`.t_shopping_shop_store sst WHERE sst.id = sss.store_id) AS parentName\n" +
                    "FROM\n" +
                    "`ctshk-goods`.t_shopping_shop_store sss\n" +
                    "WHERE\n" +
                    "sss.email = #{email} and sss.password = #{password}"
    })
    ShoppingShopStore selectShopByEmail(@Param("email") String contactNumber,@Param("password") String password);

    /**
     * 根据手机号或邮箱验证用户是否存在
     * @param contactNumber
     * @return
     */
    @Select({
            "SELECT * FROM `ctshk-goods`.t_shopping_shop_store WHERE email = #{contactNumber} or customer_service_hotline = #{contactNumber}"
    })
    ShoppingShopStore selectShopByPhoneOrEmail(String contactNumber);

    /**
     * 根据id查询小店信息
     * @param id
     * @return
     */
    @Select({
            "SELECT sss.id,sss.store_invitation_code,sss.contact_mobile_area_code,sss.head_portrait_url," +
                    "sss.personnel_type,sss.store_type,sss.store_name,sss.customer_service_hotline,sss.email," +
                    "sss.corporate_name,sss.shop_name,sss.shop_id,sss.staff_id,sss.staff_name,sss.staff_type,sss.store_id," +
                    "sss.shop_type,sss.status,sss.create_id,sss.modified_id,sss.is_deleted,sss.facebook,sss.whatsapp,sss.line," +
                    "(SELECT sst.staff_name FROM `ctshk-goods`.t_shopping_shop_store sst WHERE sst.id = sss.store_id) AS parentName," +
                    "sss.wechat,sss.password,sss.gmt_create,sss.gmt_modified " +
                    "FROM `ctshk-goods`.t_shopping_shop_store sss " +
                    "WHERE sss.id=#{id}"
    })
    ShoppingShopStore selectShopById(@Param("id") Long id);

    /**
     * 通过名字查询小店
     * @param name
     * @return
     */
    @Select({
            "SELECT `id`, `store_invitation_code`, `head_portrait_url`, `store_type`, `store_name`, `customer_service_hotline`, `email`, `corporate_name`, `shop_name`, `shop_id`, `staff_id`, `staff_name`, `staff_type`, `store_id`, `shop_type`, `status`, `gmt_create`, `create_id`, `gmt_modified`, `modified_id`, `is_deleted`, `personnel_type`, `facebook`, `line`, `whatsapp`, `wechat`, `password`, `contact_mobile_area_code`, `store_url` " +
                    "FROM `ctshk-goods`.t_shopping_shop_store " +
                    "WHERE (store_name = #{name})"
    })
    ShoppingShopStore selectShopByName(@Param("name") String name);

    /**
     * 通过名字和邀请码查询小店
     * @param code
     * @param name
     * @return
     */
    @Select({
            "SELECT `id`, `store_invitation_code`, `head_portrait_url`, `store_type`, `store_name`, `customer_service_hotline`, `email`, `corporate_name`, `shop_name`, `shop_id`, `staff_id`, `staff_name`, `staff_type`, `store_id`, `shop_type`, `status`, `gmt_create`, `create_id`, `gmt_modified`, `modified_id`, `is_deleted`, `personnel_type`, `facebook`, `line`, `whatsapp`, `wechat`, `password`, `contact_mobile_area_code`, `store_url` " +
                    "FROM `ctshk-goods`.t_shopping_shop_store " +
                    "WHERE (store_name = #{name} AND store_invitation_code = #{code})"
    })
    ShoppingShopStore selectShopByCodeAndName(@Param("code") String code,@Param("name") String name);
}
